How do I get a CDATA section in results when using a SELECT ... FOR XML PATH? (SQL 2005 SP1)

Thanks.

You cannot get CDATA sections... CDATA sections are only useful when you handauthor data and have no semantic meaning and are not being preserved by the XML data type either.

Could you please provide me with the reasons why you want to generate them?

Thanks

Michael

Our client insists on the CDATA sections. I had to change the query from FOR XML PATH to FOR XML EXPLICIT and then use the CDATA directive.

I'm struggling with this myself... You never know what your uses throw into a DB.

I had a 3000 line for xml explicit query that I converted to use xml path. Using xml path I have created a query that is only30 lines, but that doesn't matter if i can't get the data back with cdata nodes. I've got to use the bloated query.

Has anyone solved this madness with a work around?

Thisseems to be a common question posted by many. Why MS wouldn't create a cdata() method is beyond me!

The XML datatype does not preserve CDATA sections... it is part of the W3C XQuery recommendation that it does not preserve CDATA section.

Again, why do you need the CDATA section? There is no semantic difference in the data that you store. Could you please provide me with a clear use case that shows the need for generating CDATA sections?

Thanks

Michael - MSFT

Here is one.

I am a controls engineer and I am using a C# program to fill a sql database with code. I am then writing a query to build a xml file from this table which will help me auto-write a majority of my program code. If I can't write CDATA, I can't auto fill my comments which would be dissapointing.

Dear MRys,

In the current implementation of ObamaCare XML is a major part of the Standard of exchanging information between providers.  Document attachments are part of the specification.  Things like DiCom Images, PDF's, and Scanned Documents.  

With current method that SQL Server is handling CDATA a 3 megabyte Dicom Image that is embedded in a CCD document is being processed for entities and it should not the over head related to the byte for byte IO and the fact that I want the maintain the true integrity of the data I have to base64encode the data and store it that way just seems redundant and overboard if the specification was adhered to.  And CDATA was let alone and intact in the XML Data object in SQL Server.

While I just recently stumble on this issue through some testing on a new Database model very XML concentric , it frustrates me to see that the Standard is not supported.  And forces me to separate components of the original XML outside of SQL Server creates a overly complicated solution to storing a document that is valid XML into multiple tables and multiple columns just to prevent this behavior.

If there is anyway we can get an option on the insert to not parse CDATA that would be awsome or just support the standard as it is written.

Thanks,

Ron 

• Edited by Ron A Wilson Friday, February 22, 2013 11:00 PM

The actual W3C recommendation requires CDATA to be outputted if required by the end used.  For some reason I'm not surprised Microsoft hired closed minded devs that wouldn't think this is an important capability.  Some of us like to generate human readable XML as well as control file size expansion created when special characters are encoded.

http://www.w3.org/TR/xslt-xquery-serialization-31/#XML_CDATA-SECTION-ELEMENTS